import decimal, sys, os, traceback, StringIO
import datetime as dt
from datetime import datetime
import xlwt as xl
import helpers
from Deco import accepts, returns
from decimal import Decimal
from color_converter import *
from django.utils import encoding
import types

QUERY_ADD_RIGHT, QUERY_ADD_DOWN = range(2)
REPORT_VERT,REPORT_HORZ =  range(2)
ITEM_DATE, ITEM_TIME, ITEM_DATETIME, ITEM_INT, ITEM_FLOAT, ITEM_DECIMAL, \
                                ITEM_UNICODE, ITEM_BOOL, ITEM_HEADER, \
                                ITEM_IMAGE = range(10)
STYLE_FACTORY = {}
FONT_FACTORY = {}
EXCEL_COLUMN_LIMIT = 256

class ReportStyle(object):
    
    orientation = REPORT_VERT
    
    def set_orientation(self,orientation): self.orientation = orientation
    
    def get_orientation(self): return self.orientation
        

            
class CellFormatter(object):
    """
    FLAGS
    =====

    norepeat  : Do not repeat if previous cell value is the same
    spreadall : take related cross-query and populate accross
    
    """
    
    def __init__(self,**kw):
        self.cellformats = {}
        if kw.has_key("norepeat"):
            fields = helpers.get_items(kw["norepeat"])
            self.cellformats['norepeat'] = fields
        if kw.has_key("spreadall"):
            fields = helpers.get_items(kw["spreadall"])
            self.cellformats['spreadall'] = fields
    
    def getCellFormats(self):
        return self.cellformats


class Item(object):
    """
    abstract Item bridges the gap between different singular data pieces and a generic type
    """
    __val = None
    __type = None
    __percision = 0
    __name = ""
    
    width = None
    height = None
    
    def __unicode__(self):
        return u"Item (%s): %s" % (self.__type,self.__val)

    def __repr__(self):
        return self.__unicode__()

    def image(self,force_height=None,force_width=None):
        if not os.path.exists(self.__val):
            raise Exception("Not valid path to image: '%s'" % self.__val)
        self.__type = ITEM_IMAGE
        self.width = force_height
        self.width = force_width

    def __init__(self, val, formatter=None, header=False,force_height=None,force_width=None,
                 name=""):
        """
            val: The value of the item
            header: bool flag if Item is a header
        """
        self.__name = name
        
        self.width = force_width
        self.height = force_height
        
        self.__val = val
        
        try:
            obtype = type(self.__val)
        except:
            obtype = unicode
        
        if header == True:
            self.__type = ITEM_HEADER
        elif obtype == unicode:
            self.__type = ITEM_UNICODE
        elif obtype  == int:
            self.__type = ITEM_INT
        elif obtype == bool:
            self.__type = ITEM_BOOL
        elif obtype == float:
            self.set_percision(0 - int(Decimal(str(val)).as_tuple()[2]) )
            self.__type = ITEM_FLOAT
        elif obtype == decimal.Decimal:
            self.set_percision( 0 - int(val.as_tuple()[2]) )
            self.__type = ITEM_DECIMAL
        elif obtype == dt.datetime:
            self.__type = ITEM_DATETIME
        elif obtype == dt.date:
            self.__type = ITEM_DATE
        elif obtype == dt.time:
            self.__type = ITEM_TIME
            
            
        self.formatter = formatter
        
    def get_formatter(self):
        return self.formatter
    
    def set_formatter(self,formatter):
        self.formatter = formatter
        
    def set_percision(self,num):
        self.__percision = num
  
    def get_percision(self):
        return self.__percision 

    def get_type(self):
        return self.__type

    def get_name(self):
        return self.__name

    def get_value(self):
        if self.__type == ITEM_INT:
            return int(self.__val)
        elif self.__type == ITEM_FLOAT or self.__type == ITEM_DECIMAL:
            return float(self.__val)
        elif self.__type == ITEM_DATE:
            return dt.datetime.combine(self.__val,dt.time(0,0,0))
        elif self.__type == ITEM_DATETIME or self.__type == ITEM_TIME:
            return self.__val
        else:
            #return unicode(self.__val)
            return encoding.smart_unicode(self.__val)
    
    def get_length(self):
        if type(self.__val) == str:
             return len( str(self.__val) )
        elif type(self.__val) == unicode:
             return len( self.__val )
        else:
             return 10 

class Summary(object):
    def __init__(self, key=None, total_on=None):
        self.__key = key # key to watch for changing
        self.__key_index = None
        self.last = None
        self.total_on = total_on
        self.running_total = []
        self.saved_total = None
        self.last_inst = {}
    
    def check(self,inst):
            
        if not self.last:
            self.last_inst = inst
            self.last = inst[self.__key]
        elif inst[self.__key] != self.last:
            self.last = inst[self.__key]
            self.saved_total = sum(self.running_total)
            self.running_total = []
            self.running_total.append(inst[self.total_on])
            return True

        self.last_inst = inst
        if self.total_on:
            self.running_total.append(inst[self.total_on])
        return False
    
    def get_last(self):
        out = self.last_inst
        for k in out.keys():
            if self.total_on and k == self.total_on:
                out[k] = self.saved_total
            
        return out
    
    def final(self):
        self.saved_total = sum(self.running_total)
        return self.get_last()
        
    
        
        
class BaseStyle(object):
    """
    dictionary of common style properties
    """
    __style = {}
    __pattern = []
    __alignment = []
    __border = []
    __font = []
    
    def __init__(self,**kwargs):
        
        """
        Keyword arguments:
        
        pattern_color = 6 character hexidecimal color (background color of cell) *Must be set with pattern_border
        pattern_border = 0 or 1 (Solid: 1, None: 0) *Must be set with pattern_color
        
        border_style = int (size of border in points) *Must be set with border_color
        border_color = 6 character hexidecimal color *Must be set with border_style
        
        alignment settings:
        
        dire: int *see below
        horz: int *see below
        inde: int *see below
        merg: int *see below
        orie: int *see below
        rota: int *see below
        shri: int *see below
        vert: int *see below
        wrap: int *see below
        
        font_color = 6 character hexidecimal color
        bold = True/False (font property)
        italic = True/False (font property)
        shadow = True/False (font property)
        outline = True/False (font property)
        struck_out = True/False (font property)
        underline = True/False (font property)
        height = int (200 = 10pt, 160 = 8pt)
        
        **************
        
        BORDER STYLES:
        THIN = 1
        MEDIUM = 2
        DASHED = 3
        DOTTED = 4
        THICK = 5
        DOUBLE = 6
        HAIR = 7
        MEDIUM_DASHED = 8
        THIN_DASH_DOTTED = 9
        MEDIUM_DASH_DOTTED = 10
        THIN_DASH_DOT_DOTTED = 11
        MEDIUM_DASH_DOT_DOTTED = 12
        SLANTED_MEDIUM_DASH_DOTTED = 13
        
        ***************
        *ALIGNMENT SETTINGS:
        
        "dire":
        DIRECTION_GENERAL = 0
        DIRECTION_LR = 1
        DIRECTION_RL = 2
        
        "horz":
        HORZ_GENERAL = 0
        HORZ_LEFT = 1
        HORZ_CENTER = 2
        HORZ_RIGHT = 3
        HORZ_FILLED = 4
        HORZ_JUSTIFIED = 5
        HORZ_CENTER_ACROSS_SEL = 6
        HORZ_DISTRIBUTED = 7
        
        "inde": ?
        "merg": ?
        
        "orie":
        ORIENTATION_NOT_ROTATED = 0
        ORIENTATION_STACKED = 1
        ORIENTATION_90_CC = 2
        ORIENTATION_90_CW = 3
        
        "rota":
        ROTATION_0_ANGLE = 0
        ROTATION_STACKED = 255
        
        "shri":
        NOT_SHRINK_TO_FIT = 0
        SHRINK_TO_FIT = 1
        
        "vert":
        VERT_TOP = 0
        VERT_CENTER = 1
        VERT_BOTTOM = 2
        VERT_JUSTIFIED = 3
        VERT_DISIRIBUTED = 4
        
        "wrap":
        NOT_WRAP_AT_RIGHT = 0
        WRAP_AT_RIGHT = 1
        
        """
        if kwargs.has_key('pattern_color') and kwargs.has_key('pattern'):
            self.set_pattern(pattern=kwargs['pattern'],pattern_color=kwargs['pattern_color'])        
        if kwargs.has_key('border_style') and kwargs.has_key('border_color'):
            self.set_border(border_style=kwargs['border_style'],border_color=kwargs['border_color'])
            
        dire=0
        horz=0
        inde=0
        merg=0
        orie=0
        rota=0
        shri=0
        vert=2
        wrap=0
        
        if kwargs.has_key('dire'):
            dire = kwargs['dire']
        if kwargs.has_key('horz'):
            horz = kwargs['horz']
        if kwargs.has_key('inde'):
            inde = kwargs['inde']
        if kwargs.has_key('merg'):
            merg = kwargs['merg']
        if kwargs.has_key('orie'):
            orie = kwargs['orie']
        if kwargs.has_key('rota'):
            rota = kwargs['rota']
        if kwargs.has_key('shri'):
            shri = kwargs['shri']
        if kwargs.has_key('vert'):
            vert = kwargs['vert']
        if kwargs.has_key('wrap'):
            wrap = kwargs['wrap']
        self.set_alignment(dire,horz,inde,merg,orie,rota,shri,vert,wrap)
        
        font_color = '000000'
        height = 200
        bold = False
        italic = False
        shadow = False
        outline = False
        struck_out = False
        underline = False
        
        if kwargs.has_key('font_color'):
            font_color=kwargs['font_color']
        if kwargs.has_key('bold'):
            bold=kwargs['bold']      
        if kwargs.has_key('italic'):
            italic=kwargs['italic']
        if kwargs.has_key('shadow'):
            shadow=kwargs['shadow']
        if kwargs.has_key('outline'):
            outline=kwargs['outline']       
        if kwargs.has_key('struck_out'):
            struck_out=kwargs['struck_out']
        if kwargs.has_key('underline'):
            underline=kwargs['underline']
        if kwargs.has_key('height'):
            height=kwargs['height']
        self.set_font(font_color=font_color,bold=bold,italic=italic,outline=outline,struck_out=struck_out,underline=underline,height=height)
            
    def set_pattern(self,pattern_color=False,pattern=False):
        self.__pattern = []
        if pattern_color:
            _pattern_back_colour = ('_pattern_back_colour',get_closest_rgb_match(self.get_color_dict(), pattern_color))
            _pattern_fore_colour = ('_pattern_fore_colour',get_closest_rgb_match(self.get_color_dict(), pattern_color))
            self.__pattern.extend((_pattern_back_colour,_pattern_fore_colour))
        if pattern:
            pattern_style = ('pattern',pattern)
            self.__pattern.append(pattern_style)
            
    def set_alignment(self,dire=0,horz=0,inde=0,merg=0,orie=0,rota=0,shri=0,vert=2,wrap=0):
        align_dire = ('dire',dire)
        align_horz = ('horz',horz)
        align_inde = ('inde',inde)
        align_merg = ('merg',merg)
        align_orie = ('orie',orie)
        align_rota = ('rota',rota)
        align_shri = ('shri',shri)
        align_vert = ('vert',vert)
        align_wrap = ('wrap',wrap)
        self.__alignment.extend((align_dire,align_horz,align_inde,align_merg,align_orie,align_rota,align_shri,align_vert,align_wrap))
     
    def set_border(self,border_color=False,border_style=False):
        self.__border = []
        size = 0
        color = 'FFFFFF'
        if border_style:
            size = border_style
        if border_color:
            color = get_closest_rgb_match(self.get_color_dict(), border_color)
        border_bottom = ('bottom',size)
        border_bottom_colour = ('bottom_colour',color)
        border_left = ('left',size)
        border_left_colour = ('left_colour',color)
        border_right = ('right',size)
        border_right_colour = ('right_colour',color)
        border_top = ('top',size)
        border_top_colour = ('top_colour',color)
        self.__border.extend((border_bottom,border_bottom_colour,border_left,border_left_colour,border_right,border_right_colour,border_top,border_top_colour))

    def set_font(self,font_color=False,bold=False,italic=False,outline=False,shadow=False,struck_out=False,underline=False,height=False):
        self.__font = []
        if font_color:
            font_colour = ('colour_index',get_closest_rgb_match(self.get_color_dict(), font_color))
            self.__font.append(font_colour)
        if bold:
            font_bold = ('bold',bold)
            self.__font.append(font_bold)
        if italic:
            font_italic = ('italic',italic)
            self.__font.append(font_italic)
        if outline:
            font_outline = ('outline',outline)
            self.__font.append(font_outline)
        if shadow:
            font_shadow = ('shadow',shadow)
            self.__font.append(font_shadow)
        if struck_out:
            font_struck_out = ('struck_out',struck_out)
            self.__font.append(font_struck_out)
        if underline:
            font_underline = ('underline',underline)
            self.__font.append(font_underline)
        if height:
            font_height = ('height',height)
            self.__font.append(font_height)
    
    def get_style(self):
        return self.__style
    
    def get_pattern(self):
        return self.__pattern
    
    def get_alignment(self):
        return self.__alignment
    
    def get_border(self):
        return self.__border
    
    def get_font(self):
        return self.__font
    
    def get_style_dict(self):
        self.__style = {}
        self.__style['pattern'] = self.__pattern
        self.__style['alignment'] = self.__alignment
        self.__style['border'] = self.__border
        self.__style['font'] = self.__font
        return self.__style

    def get_color_dict(self):
        abstract()
   
class ExcelStyle(BaseStyle):
    """
    Takes BaseStyle object, uses Excel color dictionary, and will create XFStyle object for Excel.
    """
    
    def get_color_dict(self):
        return get_excel_color_dict() 
        
    def get_excel_font(self,values):
        f = FONT_FACTORY.get(str(values), None)
        if f is None:
            font_key = values
            f = xl.Font()
            for attr, value in values:
                f.__setattr__(attr, value)
            FONT_FACTORY[str(values)] = f
        return f

    def get_excel_style(self):
        style = self.get_style_dict()
        s = STYLE_FACTORY.get(str(style), None)
        if s is None:
            s = xl.XFStyle()
            style_key = tuple(style.items())
            for key, values in style.items():
                if key == "pattern":
                    p = xl.Pattern()
                    for attr, value in values:
                        if attr == '_pattern_back_colour':
                            p.pattern_back_colour = value
                        elif attr == '_pattern_fore_colour':
                            p.pattern_fore_colour = value
                        else:
                            p.__setattr__(attr,value)
                    s.pattern = p
                elif key == "alignment":
                    a = xl.Alignment()
                    for attr, value in values:
                        a.__setattr__(attr,value)
                    s.alignment = a
                elif key == "border":
                    b = xl.Borders()
                    for attr, value in values:
                        b.__setattr__(attr,value)
                    s.borders = b
                elif key == "font":
                    f = self.get_excel_font(values)
                    s.font = f
            STYLE_FACTORY[str(style)] = s
        return s
    

class GridException(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)


class Grid(object):
    """
    always square, easily add items.
    
    'None' is a valid place holder for no data
    """
    
    def __init__(self):
        self.grid = []
        self.report_query_sets = []
        self.EXCEL_COLUMN_LIMIT = EXCEL_COLUMN_LIMIT

    def add_report_query_set(self, rqs):
        self.report_query_sets.append(rqs)
        rqs.set_sheet(self)
        
    def get_max_y(self):
        i = 0
        for x in self.grid:
            if len(x) > i:
                i = len(x)
        return i
    
    def square_grid(self,y):
        max_y = self.get_max_y()
        if max_y < y:
            max_y = y + 1
        i = 0
        while i < len(self.grid):
            if len(self.grid[i]) < max_y:
                diff = max_y - len(self.grid[i])
                while diff > 0:
                    self.grid[i].append(None)
                    diff -= 1
            i += 1
    
    @accepts(object,int,int,Item)
    def add(self,x,y,val):

        if x > self.EXCEL_COLUMN_LIMIT:
            raise Exception("Current Excel column limit reached, set in EXCEL_COLUMN_LIMIT")               

        xlen = len(self.grid)
        # make row(s), if needed
        if x >= xlen:
            if x == xlen:
                self.grid.append([])
                self.grid[xlen].append(None)
            else:
                i = xlen
                while x >= i:
                    self.grid.append([])
                    self.grid[i].append(None)
                    i += 1
    
        self.square_grid(y)
    
        ylen = len(self.grid[x])
        # make column(s) if needed
        if y >= ylen:
            if y == ylen:
                self.grid[x].append(None)
            else:
                i = x
                while y <= i:
                    self.grid[i].append(None)
                    i += 1
    
        if self.grid[x][y] == None:
            self.grid[x][y] = val
        else:
            raise GridException('Attempting to populate already populated cell at (%s,%s).' % (x,y))
    
    def getGrid(self):
        return self.grid


class BaseReport(object):
    """
        Abstract BaseReport Class used by various reports
        
    """
    
    def __init__(self):
        super(BaseReport, self).__init__()
        self.grids = {}
        self.current_sheet = None
        self.current_x = self.current_y = 0
        self.sheetcount = 0
        self.sheets = []
        self.current_report_query_set = None
        self.formatter = {}
        self.sums = []
        self.query_add_direction = {}
    
    
    def add_one_instance(self,inst,orientation,addheader=True):
        """
        Deprecated. Votovox is using this.
        """
        self.addQuerySet(inst,orientation,addheader)
            
    def addQuerySet(self,
                    inst,
                    orientation=REPORT_HORZ,
                    formatter=None,
                    filter=None,
                    addheader=True,
                    start_x=None,
                    start_y=None,
                    sum_formatter=None):
        """
        addQuerySet- Adds a Django QuerySet to the table.
        
            inst:  A query set
            orientation: REPORT_HORZ or  REPORT_VERT
            formatter: ExcelFormatter
            filter: ExcelFilter
            addheader: True or False
            start_x: None or int(x)
            start_y: None or int(y)
        """
        
        if not self.grids.has_key(self.current_sheet):
            self.grids[self.current_sheet] = Grid()
        
        # overwrite starting positions
        if start_x:
            self.current_x = start_x
        elif self.current_report_query_set and self.query_add_direction.has_key(self.current_sheet):
            if self.query_add_direction[self.current_sheet] == QUERY_ADD_DOWN:
                self.current_x = self.current_report_query_set.start_x 
                self.current_y = self.current_report_query_set.current_y 
                self.current_y += 1 
        else:
            self.current_x = 0

        if start_y:
            self.current_y = start_y
        elif self.current_report_query_set and self.query_add_direction.has_key(self.current_sheet):
            if self.query_add_direction[self.current_sheet] == QUERY_ADD_RIGHT:
                self.current_y = self.current_report_query_set.start_y
                self.current_x = self.current_report_query_set.current_x 
        else:
            self.current_y = 0

        self.current_report_query_set = ReportQuerySet(inst, orientation, formatter, filter, 
                                                       self.current_x, self.current_y,
                                                       sum_formatter)
        self.grids[self.current_sheet].add_report_query_set(self.current_report_query_set)
        
        headersadded = False
        i = 0
        while i<len(inst)-1:
            if addheader and not headersadded:
                iterdict = self.current_report_query_set.django_dump_obj(inst[i])
                self.current_report_query_set.do_add(iterdict, headers=True)
                headersadded = True
            iterdict = self.current_report_query_set.django_dump_obj(inst[i])
            # summary
            for sum in self.sums:
                if sum.check(iterdict):
                    self.current_report_query_set.do_add(sum.get_last(),
                                                         summary=True)
                    
            self.current_report_query_set.do_add(iterdict)
            i+=1
            
        # in case where there are zero
        if len(inst) == 0:
            # just add headers
            self.current_report_query_set.do_add({},headers=True)
            return
        
        # in case where only one record
        if addheader and not headersadded:
            iterdict = self.current_report_query_set.django_dump_obj(inst[i])
            self.current_report_query_set.do_add(iterdict,headers=True)
            headersadded = True
        iterdict = self.current_report_query_set.django_dump_obj(inst[i])
        # summary
        for sum in self.sums:
            if sum.check(iterdict):
                self.current_report_query_set.do_add(sum.get_last(),
                                                     summary=True)
                
        last = True
        if len(self.sums) > 0:
            last = False
        self.current_report_query_set.do_add(iterdict,last=last)
        
        
        # one last time  // Grand Totals will be added here        
        for sum in self.sums:
            self.current_report_query_set.do_add(sum.final(),
                                                     summary=True,last=True)
    
    def addFilter(self, **kwargs):
         self.current_report_query_set.addFilter(**kwargs)

    def clearFilters(self):
        self.current_report_query_set.clear_filters()
            
    @accepts(object,object)
    def addFormatter(self,formatter):
        self.current_report_query_set.set_formatter(formatter)
    
    def getFormatter(self):
        return self.current_report_query_set.get_formatter()
        
    @accepts(object,Summary)
    def addSummary(self,summary):
        self.sums.append(summary)
    
        

    formatters = {}
    
    def __get_format(self,key):
        if self.formatters.has_key(key):
            return self.formatters[key]
        elif self.formatters.has_key("DEFAULT"):
            return self.formatters["DEFAULT"]
        return u''
    
    
    @accepts(object,Item)
    def get_format(self,item):
        format = self.__get_format(item.get_type())
        if callable(format):
            return format(item)
        return format

    def addSheet(self,name=None):
        self.sheetcount += 1
        if name == None:
            name = "Untitled Sheet #%s" % self.sheetcount
        self.current_sheet = name
        self.sheets.append(name)
     
    def set_query_add_direction(self, direction):
        """
        defines the default direction to add queries to the sheet. so if 
        it's set to QUERY_ADD_LEFT, the next query will go to the left, 
        if set to QUERY_ADD_DOWN it will go below.
        """
        self.query_add_direction[self.current_sheet] = direction

       

    @accepts(object,(Item,object),(types.NoneType,object) )
    def addItem(self,item,formatter=None):
        if type(item) != Item:
            item = Item(item)
        if formatter:
            item.set_formatter(formatter)
        if not self.grids.has_key(self.current_sheet):
            self.grids[self.current_sheet] = Grid()
        while True:
            try:    
                self.grids[self.current_sheet].add(
                    self.current_x,
                    self.current_y,
                    item)
                break
            except GridException:
                self.current_y += 1
                
        self.change_row_position()
        
    def change_row_position(self):
        if self.current_report_query_set:
            self.current_report_query_set.change_row_position()
        
    def change_column_position(self):
        if self.current_report_query_set:
            self.current_report_query_set.change_column_position()
        
class ExcelReport(BaseReport):
    """ MS Excel Style Reports """    
    workbook = None
    tempf = None
    CHAR_WIDTH = 275
    footer = None
    header = None
    
    def __init__(self):
        self.workbook = xl.Workbook()
        self.workbook._Workbook__use_cell_values = 0
        self.cellformatters = {}
        self.on_sheet_handlers = {}
        super(ExcelReport, self).__init__()
     
    def __del__(self):
        if self.tempf and not self.tempf.closed:
            self.tempf.close()
    
    def set_on_sheet_handler(self,method,*args):
        self.on_sheet_handlers[method] = args 
    
    def addCellFormatter(self,CellFormatter):
        self.cellformatters[self.current_sheet] = CellFormatter
        
    def getCellFormatter(self):
        return self.cellformatters
    
    def nextsheet(self,name=None):
        """
        Deprecated. Votovox is using this.
        """
        self.addSheet(name)
    
    def output(self):
        """
        Deprecated. Votovox is using this.
        """
        self.writeReport(self)
        
    def set_footer(self,footer_str):
        self.footer = footer_str

    def set_header(self,header_str):
        self.header = header_str
        
    def __write_cell(self, ws, item, summary=None):
        if not item:
            self.current_report_query_set.change_column_position()
            return # skip empty cells
        header_value = None
        if item.get_type() == ITEM_HEADER:
            header_value = item.get_value()
        formatter = item.get_formatter()
        if  formatter != {} and formatter != None:
            if item.get_type() == ITEM_HEADER and formatter.header_style is not None:                            
                self.style = formatter.getHeaderStyle()
            elif formatter.col_styles != {} and formatter.getColumnStyle().has_key(header_value):
                self.style = formatter.getColumnStyle(header_value)
            elif formatter.alternate_color_style is not None and not helpers.isodd(self.current_x):
                self.style = formatter.getAlternateColorStyle()
            elif formatter.body_style is not None:
                self.style = formatter.getBodyStyle()
            else:
                self.style = ExcelStyle().get_excel_style()
            if formatter.getWidth() != {}:
                if formatter.getWidth().has_key(header_value):
                    setwidth = formatter.getWidth()[header_value]
                    self.__adjustwidth(item,setwidth)
                else:
                    self.__adjustwidth(item)
            else:
                self.__adjustwidth(item)
        else:
            self.__adjustwidth(item)
            self.style = ExcelStyle().get_excel_style()
        if self.get_format(item) != self.formatters['DEFAULT']:
            self.style.num_format_str = self.get_format(item)
        if item.get_type() == ITEM_IMAGE:
            self.__adjustwidth(item)
            self.__adjustheight(item)
            ws.insert_bitmap(item.get_value(),
                    self.current_report_query_set.current_x,
                    self.current_report_query_set.current_y,) 
        else:
            ws.write(
                self.current_report_query_set.current_x,
                self.current_report_query_set.current_y,
                item.get_value(),
                self.style
            )
        self.current_report_query_set.change_column_position()
    
    def writeReport(self):
        if not self.tempf:
            self.tempf = StringIO.StringIO()
            
        for k in self.sheets:
            if not self.current_report_query_set:
                continue
            self.current_report_query_set.current_x = 0
            self.current_report_query_set.current_y = 0
            ws = self.workbook.add_sheet(k)
            for kmeth,args in self.on_sheet_handlers.items():
                meth = getattr(ws,kmeth)
                meth(*args)
                
        
            # add headers and footers
            if self.footer:        
                ws.set_footer_str(self.footer)    
            if self.header:
                ws.set_header_str(self.header)    
                
            if not self.grids.has_key(k):
                continue
            v = self.grids[k]
            grid = v.getGrid()
            for col in grid:
                header_value = ''
                for item in col:
                    self.__write_cell(ws,item)
                  
                if item != None:
                    if item.get_formatter() != {} and item.get_formatter() != None:
                        if item.get_formatter().getFormula().has_key(header_value):
                            excel_formula = '%s(%s%s:%s%s)' % (item.get_formatter().getFormula()[header_value],
                                                               helpers.convert_to_letter(self.current_y),
                                                               '2',
                                                               helpers.convert_to_letter(self.current_y),
                                                               self.current_x)
                            self.style = ExcelStyle().get_excel_style()
                            ws.write(self.current_x,self.current_y,xl.Formula(excel_formula),self.style)
                
                        
                # now we are totally done with this row
                self.current_report_query_set.change_row_position()
                
                        
                
        self.workbook.save(self.tempf)
        return self.tempf.getvalue()


    def __func_for_nums(item):
        i = 0
        style = '0.'
        while i < item.get_percision():
            style += '0'
            i += 1
        return style



    formatters = {
        ITEM_DATETIME: 'M/D/YYYY h:mm:ss',
        ITEM_DATE: 'M/D/YYYY',
        ITEM_TIME: 'h:mm:ss',
        ITEM_FLOAT: __func_for_nums,
        ITEM_DECIMAL: __func_for_nums,
        ITEM_INT: '0',
        'DEFAULT': u'',
    }
    
    
    # Private internal methods
    def __cellcord(self):
        return self.current_x,self.current_y
    
    def __lastsheet(self):
        return self.workbook.get_sheet(self.sheetcount-2)
        
    def __cursheet(self):
        count = self.sheetcount
        return self.workbook.get_sheet(self.sheetcount)
    
    def __adjustwidth(self,item,setwidth=None):
        if setwidth:
            self.__lastsheet().col(self.current_report_query_set.current_y).width = setwidth
        if item.width:
            self.__lastsheet().col(self.current_report_query_set.current_y).width = item.width            
        newlen =  item.get_length() * self.CHAR_WIDTH
        if self.__lastsheet().col(self.current_report_query_set.current_y).width < newlen:
            self.__lastsheet().col(self.current_report_query_set.current_y).width = newlen
            
    def __adjustheight(self,item,setheight=0):
        if setheight:
            self.__lastsheet().row(self.current_report_query_set.current_x).height = setheight
        if item.height:
            self.__lastsheet().row(self.current_report_query_set.current_x).height = item.height           
        

class BaseFormatter(object):
    """
        Abstract BaseFormatter Class used by various reports
        
    """
    
    def __init__(self):
        self.header_style = None
        self.alternate_color_style = None
        self.body_style = None
        self.set_widths = {}
        self.formulas = {}
        self.col_styles = {}
    
    @accepts(object,str,int)
    def setWidth(self,fields,width):
        sortedfields = helpers.get_items(fields)
        for field in sortedfields:
            self.set_widths[field] = width
    
    def getWidth(self):
        return self.set_widths
    


class ExcelFormatter(BaseFormatter):

    @accepts(object,BaseStyle)
    def addHeaderStyle(self,style):
        self.header_style = style
        
    @accepts(object,BaseStyle)
    def addAlternateColorStyle(self,style):
        self.alternate_color_style = style
    
    @accepts(object,BaseStyle)
    def addBodyStyle(self,style):
        self.body_style = style

    @accepts(object,str,str)
    def addFormula(self,fields,function):
        sortedfields = helpers.get_items(fields)
        for field in sortedfields:
            self.formulas[field] = function

    def getFormula(self):
        return self.formulas
    
    @accepts(object,str,BaseStyle)
    def addColumnStyle(self,fields,style):
        sortedfields = helpers.get_items(fields)
        for field in sortedfields:
            self.col_styles[field] = style
            
    def getColumnStyle(self,field=None):
        if field:
            return self.col_styles[field].get_excel_style()
        else:
            return self.col_styles

    def getHeaderStyle(self):
        return self.header_style.get_excel_style()
    
    def getAlternateColorStyle(self):
        return self.alternate_color_style.get_excel_style()
    
    def getBodyStyle(self):
        return self.body_style.get_excel_style()

class ReportQuerySet(object):
    """
    defines the relationship between a QuerySet and the sheet
    allows each queryset to have its own headers and formatting
    """

    def __init__(self, query, orientation, formatter, filter,
                 start_x, start_y, sum_formatter):
        self.query = query
        self.orientation = orientation
        self.start_x = start_x
        self.start_y = start_y
        self.current_x = 0
        self.current_y = 0
        self.filter = filter
        self.formatter = formatter
        self.summary_formatter = sum_formatter
            
    def total_x(self):
        return self.start_x + self.current_x
    
    def total_y(self):
        return self.start_y + self.current_y
        
    def set_sheet(self, sheet):
        self.sheet = sheet

    def get_formatter(self):
        return self.formatter

    def clearFilters(self):
        self.filter = {}

    def django_dump_obj(self,res):
        fields = res.__dict__.copy()
        # remove hidden fields
        for key in fields.keys():
            if key.startswith('_'):
                del fields[key]


        # complex items
        complex_items = {}

        for c in self.filter.include:
            if c.find('.') > -1:
                a = res
                for part in c.split('.'):
                    a = getattr(a,part)
                    if callable(a):
                        a = a()
                complex_items[c] = a
        fields.update(complex_items)  

        # expansion
        expands = {}
        for expand in self.filter.expand:
            set = getattr(res,"%s_set" % expand)
            i = 0
            for itemexpand in  set.all():
                    
                for field,value in itemexpand.__dict__.items():
                    keyname = "%s_%s" % (field,i)
                    if keyname not in self.filter.order:
                        self.filter.order.append(keyname)
                    if keyname not in self.filter.include:
                        self.filter.include.append(keyname)
                    expands[keyname] =  value
                            
                i += 1
        fields.update(expands)

        # check for foreignkey/manytomany
        fkrelations = helpers.get_relation_list(res,FK=True)
        m2mrelations = helpers.get_relation_list(res)
        if fkrelations:
            fields.update(fkrelations)
        if m2mrelations:
            fields.update(m2mrelations)

        return fields
    
    def do_add(self, iterdict, headers=False, last=False, summary=False):

        if summary:
            formatter = self.summary_formatter
        else:
            formatter = self.formatter

        keys = iterdict.keys()
        
        if self.filter != None:
            keys = self.filter.select(keys)

        i = 0
        
        while i < len(keys):
            key = keys[i]
            item = None
            if headers:
                # header
                item = Item(key, formatter, header=True, name=key)

            elif self.filter and self.filter.alias and self.filter.alias.has_key(key):
                # resolve alias 
                item = Item(iterdict[self.filter.alias[key]], formatter,
                            name=key)

            elif not iterdict.has_key(key):
                # special hook to find item in smart functions
                item = Item(self.filter.run_smart_function(locals(),key),
                            formatter,
                            name=key)
                if not item:
                    raise Exception("Column '%s' not present. Set in filter." % key)

            if not item:    
                item = Item(iterdict[keys[i]], formatter,
                            name=key)
        
            while True:
                try:
                    self.sheet.add(
                        self.total_x(),
                        self.total_y(),
                        item)
                    break
                except GridException:
                    self.current_y += 1
                    
                    
            self.change_column_position()
            i += 1
            continue
       
        self.change_row_position(last)
        return iterdict


    def change_column_position(self):
        if self.orientation == REPORT_VERT:
            self.current_y += 1
        else:
            self.current_x += 1
    
    def change_row_position(self, last=False):
        if self.orientation == REPORT_VERT:
            self.current_x += 1
            if not last:
                self.current_y = 0
        else:
            self.current_y += 1
            if not last:
                self.current_x = 0
                        
    def reset_position(self):
        self.current_y = self.start_x
        self.current_x = self.start_y

        
class ExcelFilter(object):
    """
    Allows to apply complex filtering to exported queryset
    """

    def __init__(self, **kwargs):
        self.__smart = {}

        self.include = self.exclude = self.order = self.alias = self.expand = {}
        if kwargs.has_key('include'):
            include = kwargs['include']
            if type(include) == list:
                self.include = include
            else:
                self.include = helpers.get_items(kwargs['include'])
        if kwargs.has_key('exclude'):
            exclude = kwargs['exclude']
            if type(exclude) == list:
                self.exclude = exclude
            else:
                self.exclude = helpers.get_items(kwargs['exclude'])
        if kwargs.has_key('expand'):
            expand = kwargs['expand']
            if type(expand) == list:
                self.expand = expand
            else:
                self.expand = helpers.get_items(kwargs['expand'])
        if kwargs.has_key('order'):
            order = kwargs['order']
            if type(order) == list:
                self.order = order
            else:
                self.order = helpers.get_items(kwargs['order'])
        if kwargs.has_key('alias'):
            self.alias = kwargs['alias']


    def set_smart_function(self,key,function):
        self.__smart[key] = function

    def run_smart_function(self,context,key):
        """
            runs smart item for with that `key'
            
        """
        if not self.__smart.has_key(key):
            return None
        return self.__smart[key](context, key)
        
        
    def select(self, list):
        
        if self.order:
            list = self.order

        i = 0
        selected = []
        while i < len(list):
            if self.include:
                if list[i] not in self.include:
                    i += 1
                    continue
            if self.exclude:
                if list[i] in self.exclude:
                    i += 1
                    continue
            selected.append(list[i])
            i += 1

        return selected


def logerror(Exception, e):
    note = str(e)
    note += "\n\n"
    exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
    for line in traceback.format_exc().splitlines():
        note += "%s\n" % line
    print note

